﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DBUtility;

namespace OracleDAL.DutyAssignment
{
    public class D_DutyZhouqi
    {
        public DataTable GetList(string dutyID)
        {
            if (dutyID != "")
            {
                DataTable rtDt = new DataTable();
                string strSql = "select * from duty_arrange_zhouqi where dutyid='" + dutyID + "' order by showorder asc";
                DataTable dt = OracleHelper.Query(strSql).Tables[0];
                Model.DutyAssignment.M_Duty model = new OracleDAL.DutyAssignment.D_Duty().GetModel(dutyID);
                if (model.BANCI != "")
                {
                    int count = dt.Rows.Count / int.Parse(model.BANCI);
                    for (int i = 0; i < count; i++)
                    {
                        string title = "第" + i + "天";
                        DataColumn dc = new DataColumn();
                        rtDt.Columns.Add(dc);
                    }

                    for(int i=0;i<int.Parse(model.BANCI);i++)
                    {
                        DataRow dr=rtDt.NewRow();
                        rtDt.Rows.Add(dr);
                    }

                    string sqlBanci = "select * from duty_arrange_banci where dutyid='" + dutyID + "'";
                    DataTable dtBanci = OracleHelper.Query(sqlBanci).Tables[0];

                    for (int i = 0; i < dtBanci.Rows.Count; i++)
                    {
                        DataRow[] rows = dt.Select("banciid = '" + dtBanci.Rows[i]["id"].ToString() + "'");

                        for (int j = 0; j < rows.Length; j++)
                        {
                            rtDt.Rows[i][j] = rows[j]["userisn"].ToString();
                        }
                    }

                    return rtDt;
                }

                return null;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  增加一条数据
        /// </summary>
        public bool Add(Model.DutyAssignment.M_DutyZhouqi model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into duty_arrange_zhouqi(id,dutyid,userisn,banciid,dutyday,remark,showorder) ");
            strSql.Append("values(:ID,:DUTYID,:USERISN,:BANCIID,:DUTYDAY,:REMARK,:SHOWORDER)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50),
					new OracleParameter(":DUTYID", OracleType.VarChar,50),
					new OracleParameter(":USERISN", OracleType.VarChar,50),
					new OracleParameter(":BANCIID", OracleType.VarChar,50),
					new OracleParameter(":DUTYDAY", OracleType.VarChar,50),
					new OracleParameter(":REMARK", OracleType.VarChar,100),
					new OracleParameter(":SHOWORDER", OracleType.Number,4)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.DUTYID;
            parameters[2].Value = model.USERISN;
            parameters[3].Value = model.BANCIID;
            parameters[4].Value = model.DUTYDAY;
            parameters[5].Value = model.REMARK;
            parameters[6].Value = model.SHOWORDER;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        public DataSet GetZhouqi(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from duty_arrange_zhouqi ");
            if(strWhere != "")
            {
                strSql.Append("where " + strWhere);
            }

            strSql.Append(" order by showorder asc");

            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from duty_arrange_zhouqi ");
            if (strWhere != "")
            {
                strSql.Append(" where " + strWhere);
            }

            return OracleHelper.ExecuteSql(strSql.ToString()) > 0 ? true : false;
        }
    }
}
